﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using GqqDatabaseFirst.Products;

namespace GqqDatabaseFirst
{
    public class ProductTest
    {
        public static void DoTest()
        {
            //AddProduct();
            //UpdateProducts();
            //DisplayAllProducts();
            //GetProductsFromStorePro();
            GetCustomersFromFunction();
        }

        private static void UpdateProducts()
        {

            using (var db = new ProductsContext())
            {

                var lastProuducts = db.Products.OrderByDescending(x => x.Id).FirstOrDefault();
                lastProuducts.ProductName = "Modified";
                Console.WriteLine(db.Entry(lastProuducts).State);
                Console.WriteLine(db.Entry(lastProuducts).Property(x => x.ProductName).OriginalValue);
                Console.WriteLine(db.Entry(lastProuducts).Property(x => x.ProductName).CurrentValue);
                db.SaveChanges();
            }
        }

        public static void TestDatabaseFirst()
        {
            using (var db = new ProductsContext())
            {
                var product = db.Products.FirstOrDefault();
                Console.WriteLine("{0}-{1}", product.ProductName, product.Category);
            }
        }

        public static void AddProduct()
        {
            Products.Products pr = new Products.Products { ProductName = "Softwares", Category = ProductCategory.Produce };
            using (var db=new ProductsContext())
            {
                db.Entry(pr).State = EntityState.Added;
                var result = db.SaveChanges();
                Console.WriteLine(result);
            }
        }

        public static void AddCargo()
        { 
            //nutrition
            Cargo c = new Cargo { Name = "Grape Seeds" };
            c.Products = new Products.Products { ProductName = "Nutritions", Category = ProductCategory.Dairy };
            using (var db=new ProductsContext())
            {
                db.Entry(c).State = EntityState.Added;
                db.SaveChanges();
            }
        }

        public static void DisplayAllProducts()
        {
            using (var db = new ProductsContext())
            {
                Console.WriteLine("start display...");
                Console.WriteLine("Products infos:");
                foreach (var product in db.Products)
                {
                    Console.WriteLine("{0}--{1}", product.Id, product.ProductName);
                    if (product.Cargo.Count() > 0)
                    {
                        Console.WriteLine("\tCargo infos");
                        foreach (var cargo in db.Entry(product).Collection(x=>x.Cargo).CurrentValue)
                        {
                            Console.WriteLine("\t{0}--{1}---{2}", cargo.ProductId, cargo.Name, cargo.Message);
                        }
                    }
                    Console.WriteLine();
                }
             
            }
        }

        /// <summary>
        /// 直接读数据库。
        /// </summary>
        public static void GetProductsFromRawSql()
        {
            var sql = string.Format("select * from products where id={0}", 1);
            using (var db = new ProductsContext())
            {
                var products = db.Database.SqlQuery<Products.Products>(sql);
                foreach (var product in products)
                {
                    Console.WriteLine(product.ProductName);
                }
            }
        }

        /// <summary>
        /// 调用存储过程。
        /// </summary>
        public static void GetProductsFromStorePro()
        {
            var idParam = new SqlParameter
            {
                ParameterName = "id",
                Value = 2
            };

            var userCount = new SqlParameter
            {
                ParameterName = "userCount",
                Value = 0,
                Direction = ParameterDirection.Output
            };
            using (var ctx = new ProductsContext())
            {
                var results = ctx.Database.SqlQuery<Products.Products>(
                    "GetProductsAndUsers @id, @userCount out",
                     idParam,
                     userCount);
                var product = results.FirstOrDefault();
                var votes = (int)userCount.Value;
            }
        }

        /// <summary>
        /// 调用函数
        /// </summary>
        public static void GetCustomersFromFunction()
        {
            var minOrdersParam = new SqlParameter
            {
                ParameterName = "minOrders",
                Value = 3
            };

            using (var ctx = new ProductsContext())
            {
                var results = ctx.Database.SqlQuery<Products.Customers>(
                    "FrequentCustomers @minOrders",
                     minOrdersParam);

                var customer = results.FirstOrDefault();
            }
        }

    }
}
